import time
from datetime import datetime, date
import pandas as pd
from db_conn import pd_conn_zfw, pd_conn_wfw
import warnings

warnings.filterwarnings("ignore")


# 月份+n
def month_add(date_time, month_add_num):
    dt = str(date_time)
    total_month_num = int(dt[5:7]) + month_add_num
    result_month = total_month_num % 12 if total_month_num % 12 > 0 else 12
    year_add_num = total_month_num // 12
    return str(date(int(dt[:4]) + year_add_num, result_month, int(dt[8:10])))


# 计算两个datetime之间的工作小时差
def get_work_hour_diff(s_dt, e_dt, s_hour, e_hour):
    max_sec = (e_hour - s_hour) * 60 * 60  # 一整天的工作时长
    s_sec = s_hour * 60 * 60  # 工作开始秒数
    e_sec = e_hour * 60 * 60  # 工作结束秒数
    day_diff = (e_dt.date() - s_dt.date()).days - 1  # 两个日期相隔天数
    day_sec_diff = max(0, day_diff * max_sec)  # 两个日期相隔的整天工作时长
    # 先获得工作结束距开始时间当天的秒数，如果<0表示开始时间大于工作结束时间，则间隔替换为0；如果>整天工作时长，表示开始时间>工作开始时间，则用整天工作时长替换。
    # 最终得到开始时间当天距'工作结束'的工作时长
    s_sec_diff = min(max_sec, max(0, e_sec - (s_dt - pd.to_datetime('00:00:00')).seconds))
    # 同上，得到结束时间当天距'工作结束'的工作时长
    e_sec_diff2 = min(max_sec, max(0, e_sec - (e_dt - pd.to_datetime('00:00:00')).seconds))
    # 同上，得到结束时间当天距'工作开始'的工作时长
    e_sec_diff = min(max_sec, max(0, (e_dt - pd.to_datetime('00:00:00')).seconds - s_sec))

    if day_diff < 0:
        # 如果开始时间和结束时间在同一天，那么 工作时间间隔 = 开始时间当天距工作结束的工作时长 - 结束时间当天距'工作结束'的工作时长
        work_hour_diff = max(0, s_sec_diff - e_sec_diff2) / 60 / 60
    else:
        # 如果不是在同一天，那么 工作时间间隔 = 相隔天数整天工作时长 + 开始时间当天距工作结束的工作时长 + 结束时间当天距'工作开始'的工作时长
        work_hour_diff = (day_sec_diff + s_sec_diff + e_sec_diff) / 60 / 60

    return work_hour_diff


#
def member_ring(df_original):
    from db_conn import conm
    sql = '''
    select f_object_id f_task_id, AgentName, f_name, Ring, Begin, End
    from t_moor_record
    where AgentName in ('雷帅','姜鹏军','于阳') and f_type=2
    '''
    df_ring = pd.read_sql(sql, conm, parse_dates=['Ring', 'Begin', 'End'])

    df_m = pd.merge(df_original, df_ring, on='f_task_id', how='left')

    df = df_m.groupby('f_task_id').apply(lambda x: pd.Series({
        '通话次数0-雷帅':len(x[(x.Ring<=min(x.首访时间))&(x.AgentName=='雷帅')]),
        '通话次数0-姜鹏军':len(x[(x.Ring<=min(x.首访时间))&(x.AgentName=='姜鹏军')]),
        '通话次数0-于阳':len(x[(x.Ring<=min(x.首访时间))&(x.AgentName=='于阳')]),
        '首访时间_min':min(x.首访时间),
        '通话次数1-雷帅':len(x[(x.Ring>min(x.首访时间))&(x.Ring<=min(x.事件定责时间))&(~x.事件定责时间.isnull())&(x.AgentName=='雷帅')])+len(x[(x.Ring>min(x.首访时间))&(x.事件定责时间.isnull())&(x.AgentName=='雷帅')]),
        '通话次数1-姜鹏军':len(x[(x.Ring>min(x.首访时间))&(x.Ring<=min(x.事件定责时间))&(~x.事件定责时间.isnull())&(x.AgentName=='姜鹏军')])+len(x[(x.Ring>min(x.首访时间))&(x.事件定责时间.isnull())&(x.AgentName=='姜鹏军')]),
        '通话次数1-于阳':len(x[(x.Ring>min(x.首访时间))&(x.Ring<=min(x.事件定责时间))&(~x.事件定责时间.isnull())&(x.AgentName=='于阳')])+len(x[(x.Ring>min(x.首访时间))&(x.事件定责时间.isnull())&(x.AgentName=='于阳')]),
        '事件定责时间_min':min(x.事件定责时间),
        '通话次数2-雷帅':len(x[(x.Ring>min(x.事件定责时间))&(x.Ring<=min(x.保险定损时间))&(~x.事件定责时间.isnull())&(~x.保险定损时间.isnull())&(x.AgentName=='雷帅')])+len(x[(x.Ring>min(x.事件定责时间))&(x.保险定损时间.isnull())&(x.AgentName=='雷帅')]),
        '通话次数2-姜鹏军':len(x[(x.Ring>min(x.事件定责时间))&(x.Ring<=min(x.保险定损时间))&(~x.事件定责时间.isnull())&(~x.保险定损时间.isnull())&(x.AgentName=='姜鹏军')])+len(x[(x.Ring>min(x.事件定责时间))&(x.保险定损时间.isnull())&(x.AgentName=='姜鹏军')]),
        '通话次数2-于阳':len(x[(x.Ring>min(x.事件定责时间))&(x.Ring<=min(x.保险定损时间))&(~x.事件定责时间.isnull())&(~x.保险定损时间.isnull())&(x.AgentName=='于阳')])+len(x[(x.Ring>min(x.事件定责时间))&(x.保险定损时间.isnull())&(x.AgentName=='于阳')]),
        '保险定损时间_min':min(x.保险定损时间),
        '通话次数3-雷帅':len(x[(x.Ring>min(x.保险定损时间))&(x.Ring<=min(x.寄送发票时间))&(~x.保险定损时间.isnull())&(~x.寄送发票时间.isnull())&(x.AgentName=='雷帅')])+len(x[(x.Ring>min(x.保险定损时间))&(x.寄送发票时间.isnull())&(x.AgentName=='雷帅')]),
        '通话次数3-姜鹏军':len(x[(x.Ring>min(x.保险定损时间))&(x.Ring<=min(x.寄送发票时间))&(~x.保险定损时间.isnull())&(~x.寄送发票时间.isnull())&(x.AgentName=='姜鹏军')])+len(x[(x.Ring>min(x.保险定损时间))&(x.寄送发票时间.isnull())&(x.AgentName=='姜鹏军')]),
        '通话次数3-于阳':len(x[(x.Ring>min(x.保险定损时间))&(x.Ring<=min(x.寄送发票时间))&(~x.保险定损时间.isnull())&(~x.寄送发票时间.isnull())&(x.AgentName=='于阳')])+len(x[(x.Ring>min(x.保险定损时间))&(x.寄送发票时间.isnull())&(x.AgentName=='于阳')]),
        '寄送发票时间_min':min(x.寄送发票时间),
        '通话次数4-雷帅':len(x[(x.Ring>min(x.寄送发票时间))&(x.Ring<=min(x.赔付打款时间))&(~x.寄送发票时间.isnull())&(~x.赔付打款时间.isnull())&(x.AgentName=='雷帅')])+len(x[(x.Ring>min(x.寄送发票时间))&(x.赔付打款时间.isnull())&(x.AgentName=='雷帅')]),
        '通话次数4-姜鹏军':len(x[(x.Ring>min(x.寄送发票时间))&(x.Ring<=min(x.赔付打款时间))&(~x.寄送发票时间.isnull())&(~x.赔付打款时间.isnull())&(x.AgentName=='姜鹏军')])+len(x[(x.Ring>min(x.寄送发票时间))&(x.赔付打款时间.isnull())&(x.AgentName=='姜鹏军')]),
        '通话次数4-于阳':len(x[(x.Ring>min(x.寄送发票时间))&(x.Ring<=min(x.赔付打款时间))&(~x.寄送发票时间.isnull())&(~x.赔付打款时间.isnull())&(x.AgentName=='于阳')])+len(x[(x.Ring>min(x.寄送发票时间))&(x.赔付打款时间.isnull())&(x.AgentName=='于阳')]),
        '赔付打款时间_min':min(x.赔付打款时间)
    })).reset_index()
    df.to_csv('test.csv')
    return df


def queryStationVersionName():
    sql = """
    select f_sys_content '机器版本_',f_sys_code '机器代数' from t_sys_code where f_sys_type='stationNum' and f_is_deleted=0    
    """
    return pd_conn_wfw(sql, 'ygl_sys')

# SQL获取全部事件工单
def event_order():
    print('开始获取事件工单')
    t1 = time.time()
    sql = '''
        SELECT
            tt.*,
            if( tt.`完成时间` IS NULL, round(timestampdiff( hour, tt.`跟进时间`, now())/24,2), NULL ) '停留时长(天)',
            if(
                tt.`完成时间` is null,
                "未完结",
                if((tt.`代理人`>0 or tt.`保险`>0 or tt.`公关`>0 or tt.`公司`>0),"完结已赔付","完结未赔付")
            ) '事件状态'
        from  (
            SELECT
                t1.f_create_user_name '创建人员',
                t1.f_user_names '跟进人员',
                t1.f_content '事件描述',
                t1.f_factoryIds '站点id',
                t5.f_province '省',
                t5.f_city '市',
                t5.f_station_num '机器代数',
                t1.f_factoryNames '站点名称',
                t6.f_company '保险公司',
                t6.f_no '保险单号',
                t1.f_create_time '创建时间',
                t7.f_create_time '事件重启时间',
                MIN(t_moor_record.Ring) '第一次致电时间',
                IF (
                    (
                        date_format( t1.f_create_time, '%H:%i:%s' ) >= '09:00:00'
                        AND date_format( t1.f_create_time, '%H:%i:%s' )<= '18:00:00'
                    ),
                    t1.f_create_time,
                    IF (
                        (
                            date_format( t1.f_create_time, '%H:%i:%s' ) >= '00:00:00'
                            AND date_format( t1.f_create_time, '%H:%i:%s' )< '09:00:00'
                        ),
                        date_format( t1.f_create_time, '%Y-%m-%d 09:00:00' ),
                        date_format( date_add( t1.f_create_time, INTERVAL 1 DAY ), '%Y-%m-%d 09:00:00' ))
                ) '创建时间调整',
                t1.f_complete_time '完成时间',
                t1.f_compensate_amt '公司',
                t1.f_tikcet_amt '优惠券',
                t1.f_insurance_amt '保险',
                t1.f_pub_amt '公关',
                t1.f_agent_amt '代理人',
                t1.f_damage_area '损伤描述',
                t1.f_auto_no '车牌号码',
                t1.f_mobile '手机号码',
                t2.f_id,
                t2.f_task_id,
                t2.f_user_name '最近一次跟进人员',
                t2.f_content '跟进记录',
                t2.f_create_time '跟进时间',
                t3.time_1 '首访时间',
                t3.time_2 '事件定责时间',
                t3.time_3 '保险定损时间',
                t3.time_4 '寄送发票时间',
                t3.time_5 '赔付打款时间',
                t4.`当前节点状态`,
                t1.f_tag_names '标签内容'
            FROM
                t_task t1
                LEFT JOIN (
                    SELECT f_id, f_task_id, f_user_name, f_content, f_create_time
                    FROM t_task_record
                    WHERE f_id IN ( SELECT max( f_id ) FROM t_task_record GROUP BY f_task_id )
                ) t2 ON t1.f_id = t2.f_task_id
                LEFT JOIN (
                    SELECT
                        f_task_id,
                        max(if(f_process_code = 1, f_complete_time, NULL)) time_1,
                        max(if(f_process_code = 2, f_complete_time, NULL)) time_2,
                        max(if(f_process_code = 3, f_complete_time, NULL)) time_3,
                        max(if(f_process_code = 4, f_complete_time, NULL)) time_4,
                        max(if(f_process_code = 5, f_complete_time, NULL)) time_5
                    FROM
                        t_task_process
                    GROUP BY
                        f_task_id
                ) t3 ON t1.f_id = t3.f_task_id
                LEFT JOIN (
                    SELECT
                        t.f_task_id,
                        CASE
                            WHEN t.STATUS = 0 THEN '首访'
                            WHEN t.STATUS = 1 THEN '事件定责'
                            WHEN t.STATUS = 2 THEN '保险定损'
                            WHEN t.STATUS = 3 THEN '寄送发票'
                            WHEN t.STATUS = 4 THEN '赔付打款'
                            WHEN t.STATUS = 5 THEN '所有节点完成'
                            ELSE NULL
                        END '当前节点状态'
                    FROM (SELECT f_task_id, sum( f_status ) 'status' FROM `t_task_process` GROUP BY f_task_id) t
                ) t4 ON t1.f_id = t4.f_task_id
                LEFT JOIN t_factory t5 ON t1.f_factoryIds = t5.f_id
                left join (
                    SELECT  m1.f_company,m2.f_no,m1.f_factory_id
                    FROM t_factory_build_step_insurance m1
                    left join  `t_factory_build_step_insurance_log` m2 on m1.f_log_id = m2.f_id
                    where m1.f_log_id is not null
                )t6 on t1.f_factoryIds = t6.f_factory_id
                left join (
                    select f_task_id,f_create_time from t_task_record where f_content like '%重启任务%'
                ) t7 on t1.f_id = t7.f_task_id
                LEFT JOIN t_moor_record on t1.f_id = t_moor_record.f_object_id AND t_moor_record.f_type = 2
            WHERE
                t1.f_type = 2
                AND t1.f_del_status <> 1
            group by f_task_id
        ) tt
    '''
    df1 = pd_conn_zfw(sql)
    df1['机器代数'] = df1['机器代数'].fillna(0)
    df1['机器代数'] = [int(i) for i in df1['机器代数']]
    df2 = queryStationVersionName()
    df2['机器代数'] = [int(i) if i or i != '' else 0 for i in df2['机器代数']]
    df = df1.merge(df2, on='机器代数', how='left')
    i = list(df.columns).index('机器代数')
    df.insert(i + 1, '机器版本', df['机器版本_'])
    result = df.drop('机器版本_', axis=1)
    t2 = time.time()
    t = round(t2 - t1)
    print('事件工单获取完毕     共执行', t, 's')
    return result


def kpi(path, startTime, endTime, members):
    # SQL获取当前站点数
    def factory_count():
        sql = f"select count(1) from t_factory where f_status = 0 and f_work_start_time < '{endTime}'"
        df = pd_conn_zfw(sql)
        num = df.iloc[0, 0]
        return num

    # 获取总单量
    def order_count():
        print('开始获取单量数据')
        this_year = str(pd.to_datetime(startTime).year)
        year_start_time = this_year + '-01-01'
        sts = pd.date_range(year_start_time, endTime, freq='MS')
        df_zfw = pd.DataFrame()
        df_wfw = pd.DataFrame()
        for st in sts:
            start_time = str(st)
            end_time = month_add(st, 1)
            this_month = str(int(start_time[5:7]))
            table_name = 't_wash_order_' + this_year + '_' + this_month
            sql_wfw = f"""
                select left('{start_time}',7) mm,count(1) order_num
                from {table_name} t1 
                where 
                    (	
                        case
                            when t1.f_source = 'WASH000006' or t1.f_source = 11 then 0
                            when t1.f_source = 'WASH000002' and t1.f_order_status = 5000 and (t1.f_flag = 2 or t1.f_flag is null) then 1
                            when t1.f_source != 'WASH000002' and t1.f_order_status = 5000 then 1
                            else 0
                        end
                    ) = 1
                    and t1.f_create_time >= '{start_time}' and t1.f_create_time < least('{end_time}','{endTime}')
            """
            try:
                df_wfw1 = pd_conn_wfw(sql_wfw, 'ygl_wash_order')
            except:
                df_wfw1 = pd.DataFrame([[start_time[:7], 0]], columns=['mm', 'order_num'])
            df_wfw = df_wfw.append(df_wfw1)
            sql_zfw = f"""
                select left('{start_time}',7) mm,count(1) order_num from t_order o
                where 
                    (
                    o.f_status IN ( 50, 60 ) 
                    OR ( o.f_status = 15 AND o.f_pay_type IN ( SELECT f_pay_type FROM t_order_income_config WHERE f_status = 1 AND f_is_refund = 0 ) ) 
                    ) 
                    AND o.f_is_insider = 0 
                    and f_order_date >= '{start_time}'
                    and f_order_date < least('{endTime}','{end_time}')
            """
            try:
                df_zfw1 = pd_conn_wfw(sql_wfw, 'ygl_wash_order')
            except:
                df_zfw1 = pd.DataFrame([[start_time[:7], 0]], columns=['mm', 'order_num'])
            df_zfw = df_zfw.append(df_zfw1)
        df_zfw = df_zfw.set_index('mm')
        df_zfw.loc['total'] = df_zfw.apply(lambda x: x.sum())
        df_wfw = df_wfw.set_index('mm')
        df_wfw.loc['total'] = df_wfw.apply(lambda x: x.sum())
        print('单量数据获取完毕')
        return df_wfw, df_zfw

    # 用于计算KPI关键新增字段
    def key_value():

        # 5分首访达成率_首访时间间隔
        df = event_order()

        # 剔除异常数据
        df = df.drop(df[df.站点id == '0'].index)

        # 完成时间在统计月之后的，完成时间改为空值
        df['完成时间'] = pd.to_datetime(df['完成时间'])
        df.loc[df['完成时间'] >= pd.to_datetime(endTime), '完成时间'] = None

        df['end_time'] = df['第一次致电时间']
        df.loc[df.end_time.isnull(), 'end_time'] = df.loc[df.end_time.isnull(), '创建时间调整']
        df['end_time'] = pd.to_datetime(df['end_time'])
        df['创建时间调整'] = pd.to_datetime(df['创建时间调整'])
        col_nam = df.columns.tolist()
        col_nam.insert(0, '首访时间间隔')
        df = df.reindex(columns=col_nam)
        df['首访时间间隔'] = (df['end_time'] - df['创建时间调整']).astype('timedelta64[s]').astype(float) / 60

        # 12h定责达成率_定责时间间隔
        df['第一次致电时间'] = pd.to_datetime(df['第一次致电时间'])
        df['首访时间'] = pd.to_datetime(df['首访时间'])
        df['事件定责时间'] = pd.to_datetime(df['事件定责时间'])
        col_nam = df.columns.tolist()
        col_nam.insert(1, '定责时间间隔')
        df = df.reindex(columns=col_nam)
        df['定责时间'] = df['第一次致电时间']
        df.loc[df['第一次致电时间'].isnull(), '定责时间'] = df.loc[df['第一次致电时间'].isnull(), '首访时间']
        # df['定责时间间隔'] = (df['事件定责时间'] - df['定责时间']).astype('timedelta64[s]').astype(float) / 60 / 60
        df.loc[df['事件定责时间'].notnull(), '定责时间间隔'] = df.loc[df['事件定责时间'].notnull(), :].apply(
            lambda x: get_work_hour_diff(x['定责时间'], x['事件定责时间'], 9, 18), axis=1)
        df.loc[df['事件定责时间'].isnull(), '定责时间间隔'] = 48

        # 超时率_赔付时间间隔
        df['保险定损时间'] = pd.to_datetime(df['保险定损时间'])
        df['赔付打款时间'] = pd.to_datetime(df['赔付打款时间'])
        col_nam = df.columns.tolist()
        col_nam.insert(2, '赔付时间间隔')
        df = df.reindex(columns=col_nam)
        df['赔付时间间隔'] = (df['赔付打款时间'] - df['保险定损时间']).astype('timedelta64[s]').astype(float) / 60 / 60 / 24
        df.loc[df['赔付打款时间'].isnull(), '赔付时间间隔'] = 30

        # 理赔率_是否理赔
        col_nam = df.columns.tolist()
        col_nam.insert(3, '是否理赔')
        df = df.reindex(columns=col_nam)
        df.loc[df[['公司', '保险', '公关', '代理人']].notnull().any(axis=1), '是否理赔'] = '是'

        # 平均完结时长_完结时长
        col_nam = df.columns.tolist()
        col_nam.insert(4, '完结时长')
        df = df.reindex(columns=col_nam)
        df.loc[df['完成时间'].notnull(), '完结时长'] = (
                                                   df.loc[df['完成时间'].notnull(), '完成时间'] - df.loc[
                                                   df['完成时间'].notnull(), '创建时间调整']
                                               ).astype('timedelta64[s]').astype(float) / 60 / 60 / 24
        df.loc[df['完结时长'] < 0, '完结时长'] = 0

        return df

    # KPI及明细
    def kpi_detail():

        df = key_value()
        fc = factory_count()
        df_oc_z, df_oc_w = order_count()
        oc = df_oc_z.loc[startTime[:7], 'order_num'] + df_oc_w.loc[startTime[:7], 'order_num']
        oc_y = df_oc_z.loc['total', 'order_num'] + df_oc_w.loc['total', 'order_num']

        # 当月新增明细
        df_new = df[(df['创建时间'] >= pd.to_datetime(startTime)) & (df['创建时间'] < pd.to_datetime(endTime))]

        # 当月需完成明细
        df_to_finish = df[
            (df['创建时间'] < pd.to_datetime(endTime))
            &
            ((df['完成时间'] >= pd.to_datetime(startTime)) | (df['完成时间'].isnull()))
            ]

        # 当年需完成明细
        this_year = str(pd.to_datetime(startTime).year)
        year_start_time = this_year + '-01-01'
        df_to_finish_year = df[
            (df['创建时间'] < pd.to_datetime(endTime))
            &
            ((df['完成时间'] >= pd.to_datetime(year_start_time)) | (df['完成时间'].isnull()))
            ]

        # 部门KPI
        cycle = startTime + '~' + endTime
        df_kpi = pd.DataFrame([], index=[cycle])

        # 5分首访达成率
        df_kpi.loc[cycle, '5分首访达成率'] = df_new[df_new['首访时间间隔'] < 5]['创建时间'].count() / df_new['创建时间'].count()

        # 12h定责达成率
        df_kpi.loc[cycle, '12h定责达成率'] = df_new[df_new['定责时间间隔'] <= 12]['创建时间'].count() / df_new['创建时间'].count()

        # 理赔均站
        df_kpi.loc[cycle, '理赔均站({})'.format(fc)] = df_to_finish[df_to_finish['完成时间'].notnull()]['代理人'].sum() / fc

        # 当年理赔均站
        df_kpi.loc[cycle, '当年理赔均站({})'.format(fc)] = df_to_finish_year[df_to_finish_year['完成时间'].notnull()][
                                                         '代理人'].sum() / fc

        # 理赔均单
        df_kpi.loc[cycle, '理赔均单({})'.format(oc)] = df_to_finish[df_to_finish['完成时间'].notnull()]['代理人'].sum() / oc

        # 当年理赔均单
        df_kpi.loc[cycle, '当年理赔均单({})'.format(oc_y)] = df_to_finish_year[df_to_finish_year['完成时间'].notnull()][
                                                           '代理人'].sum() / oc_y

        # 结案率
        df_kpi.loc[cycle, '结案率'] = df_to_finish[df_to_finish['完成时间'] < pd.to_datetime(endTime)
                                                ]['创建时间'].count() / df_to_finish['创建时间'].count()

        # 平均完结时长
        df_kpi.loc[cycle, '平均完结时长'] = df_to_finish[
                                          df_to_finish['完成时间'] < pd.to_datetime(endTime)
                                          ]['完结时长'].sum() / \
                                      df_to_finish[df_to_finish['完成时间'] < pd.to_datetime(endTime)]['完成时间'].count()

        # 理赔率
        df_kpi.loc[cycle, '理赔率'] = df_to_finish[
                                       df_to_finish['完成时间'] < pd.to_datetime(endTime)
                                       ]['代理人'].count() / df_to_finish['创建时间'].count()

        # 客诉率
        df_kpi.loc[cycle, '客诉率'] = df_new['创建时间'].count() / oc

        # 免赔额
        df_kpi.loc[cycle, '免赔额'] = df_to_finish[
            df_to_finish['完成时间'] < pd.to_datetime(endTime)
            ]['代理人'].sum()

        # 保险承担
        df_kpi.loc[cycle, '保险承担'] = df_to_finish[
            df_to_finish['完成时间'] < pd.to_datetime(endTime)
            ]['保险'].sum()

        # 事件量
        df_kpi.loc[cycle, '事件量'] = df_new['创建时间'].count()

        # 超时率
        df_kpi.loc[cycle, '超时率'] = df_to_finish[
                                       (df_to_finish['赔付时间间隔'] >= 30) & (df_to_finish['完成时间'] < pd.to_datetime(endTime))
                                       ]['保险定损时间'].count() / \
                                   df_to_finish[df_to_finish['完成时间'] < pd.to_datetime(endTime)]['保险定损时间'].count()

        # 员工KPI_共同
        df_kpi_m = pd.DataFrame([], index=members)
        for member in df_kpi_m.index:
            # 5分首访达成率
            df_kpi_m.loc[member, '5分首访达成率'] = df_new[
                                                  (df_new['跟进人员'].str.contains(member)) & (df_new['首访时间间隔'] < 5)
                                                  ]['跟进人员'].count() / df_new[df_new['跟进人员'].str.contains(member)][
                                                  '跟进人员'].count()

            # 12h定责达成率
            df_kpi_m.loc[member, '12h定责达成率'] = df_new[
                                                   (df_new['跟进人员'].str.contains(member)) & (df_new['定责时间间隔'] <= 12)
                                                   ]['跟进人员'].count() / df_new[df_new['跟进人员'].str.contains(member)][
                                                   '跟进人员'].count()

        df_new.drop(['end_time', '定责时间'], axis=1, inplace=True)
        df_to_finish.drop(['end_time', '定责时间'], axis=1, inplace=True)
        df_to_finish_year.drop(['end_time', '定责时间'], axis=1, inplace=True)
        return df_kpi, df_kpi_m, df_new, df_to_finish, df_to_finish_year

    # 结果导出
    def result_export():

        t1 = time.time()

        title_time1 = startTime[-5:-3] + startTime[-2:]
        title_time2 = endTime[-5:-3] + endTime[-2:]
        title_time3 = datetime.strftime(datetime.now(), '%M%S')

        writer = pd.ExcelWriter(r'{}\售后KPI{}~{}({}).xlsx'.format(path, title_time1, title_time2, title_time3))

        df_kpi, df_kpi_m, df_new, df_to_finish, df_to_finish_year = kpi_detail()

        df_new_ring = df_new.merge(member_ring(df_new), on='f_task_id', how='left').reset_index()
        df_to_finish_ring = df_to_finish.merge(member_ring(df_to_finish), on='f_task_id', how='left').reset_index()
        df_to_finish_year_ring = df_to_finish_year.merge(member_ring(df_to_finish_year), on='f_task_id', how='left').reset_index()

        df_kpi.to_excel(writer, sheet_name='部门KPI')
        df_kpi_m.to_excel(writer, sheet_name='员工KPI_共同')
        df_new_ring.to_excel(writer, sheet_name='本月新增')
        df_to_finish_ring.to_excel(writer, sheet_name='本月需完成')
        df_to_finish_year_ring.to_excel(writer, sheet_name='当年需完成')

        writer.save()
        writer.close()

        t2 = time.time()

        print('DOWN!', '共执行', round(t2-t1, 0), 's')

    return result_export()


def factory_count(endTime):
    sql = f"select count(1) from t_factory where f_status = 0 and f_work_start_time < '{endTime}'"
    return pd_conn_zfw(sql)


if __name__ == '__main__':
    print(factory_count('2022-08-01'))